********************************************************************************
** 	TITLE:		UK87_results                                                  ** 
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       1918-2019election_results_by_pcon         		              **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Open log file

capture log close
log using "UK87_results.smcl", replace

* Import the dataset

import excel "1918-2019election_results_by_pcon.xlsx", cellrange(B5:AQ654) sheet(1987) clear

* Reshape

drop G J M P S V Y AB AE AH AK AL AO

rename B id
rename C constituency_name
rename D county
rename E region
rename F electorate
rename H votes1
rename I share1
rename K votes2
rename L share2
rename N votes3
rename O share3
rename Q votes4
rename R share4
rename T votes5
rename U share5
rename W votes6
rename X share6
rename Z votes7
rename AA share7
rename AC votes8
rename AD share8
rename AF votes9
rename AG share9
rename AI votes10
rename AJ share10
rename AM votes11
rename AN share11
rename AP totalvotes
rename AQ turnout

reshape long votes share, i(id) j(party_code)  

gen party_name = " "

replace party_name = "Conservative" if party_code == 1 
replace party_name = "Labour" if party_code == 2
replace party_name = "Alliance" if party_code == 3
replace party_name = "SNP/Plaid Cymru" if party_code == 4
replace party_name = "DUP" if party_code == 5 
replace party_name = "OUP" if party_code == 6
replace party_name = "Sinn Fein" if party_code == 7
replace party_name = "SDLP" if party_code == 8
replace party_name = "Alliance (Northern Ireland)" if party_code == 9
replace party_name = "Workers' Party" if party_code == 10
replace party_name = "Other" if party_code == 11

drop party_code

gen party_code = .

replace party_code = 1 if party_name == "Conservative"
replace party_code = 2 if party_name == "Labour"
replace party_code = 3 if party_name == "Alliance"
replace party_code = 4 if party_name == "SNP/Plaid Cymru"
replace party_code = -9999 if region == "Northern Ireland"
replace party_code = 9 if party_code > 4
 
* Generate rank of parties

gsort constituency_name -votes
bysort constituency_name : gen rank = _n

gen first = party_name if rank == 1
gen second = party_name if rank == 2
gen third = party_name if rank == 3

bysort constituency_name : gen winner87 = first[1]
bysort constituency_name : gen runnerup87 = second[2]
bysort constituency_name : gen thirdplace87 = third[3]

* Generate margins of victory

bysort constituency_name : gen margin = (share[1] - share[2])*100

* Generate effective number of electoral parties

gen sqrvotepr = (share)^2

bysort constituency_name : egen sumsqrvotepr = sum(sqrvotepr)

gen enep = 1/sumsqrvotepr

* Labelling parties

replace party_name = "1" if party_name == "Conservative"
replace party_name = "2" if party_name == "Labour"
replace party_name = "3" if party_name == "Alliance"
replace party_name = "4" if party_name == "SNP/Plaid Cymru"
replace party_name = "9" if party_name!="1" & party_name!="2" & party_name!="3" & party_name!="4"

destring (party_name), replace

replace winner87 = "1" if winner87 == "Conservative"
replace winner87 = "2" if winner87 == "Labour"
replace winner87 = "3" if winner87 == "Alliance"
replace winner87 = "4" if winner87 == "SNP/Plaid Cymru"
replace winner87 = "9" if winner87!="1" & winner87!="2" & winner87!="3" & winner87!="4"

destring (winner87), replace

replace runnerup87 = "1" if runnerup87 == "Conservative"
replace runnerup87 = "2" if runnerup87 == "Labour"
replace runnerup87 = "3" if runnerup87 == "Alliance"
replace runnerup87 = "4" if runnerup87 == "SNP/Plaid Cymru"
replace runnerup87 = "9" if runnerup87!="1" & runnerup87!="2" & runnerup87!="3" & runnerup87!="4"

destring (runnerup87), replace

replace thirdplace87 = "1" if thirdplace87 == "Conservative"
replace thirdplace87 = "2" if thirdplace87 == "Labour"
replace thirdplace87 = "3" if thirdplace87 == "Alliance"
replace thirdplace87 = "4" if thirdplace87 == "SNP/Plaid Cymru"
replace thirdplace87 = "9" if thirdplace87!="1" & thirdplace87!="2" & thirdplace87!="3" & thirdplace87!="4"

destring (thirdplace87), replace

label define party 1 "Conservative" 2 "Labour" 3 "Alliance" 4 "SNP/Plaid Cymru" 9 "Other"
label values party_name winner87 runnerup87 thirdplace87 party
 
* Selecting, ordering, and sorting variables

keep id constituency_name party_name rank votes share margin enep winner87 runnerup87 thirdplace87

order id constituency_name party_name rank votes share margin enep winner87 runnerup87 thirdplace87

sort id rank

drop if rank > 3

* Drop duplicates

duplicates tag id, gen(dup)
duplicates drop id, force
drop dup

* Saving file

save UK87_results, replace

log close